import java.sql.*;
import java.util.List;

//查询，登入
public class JDBC1 {
    public static Connection getConnection(String driverName, String url,String userName,String possword) throws Exception {
        Class.forName(driverName);
        return DriverManager.getConnection(url,userName,possword);
    }
    //确认学生账户是否正确
    public static boolean find1(String ZHH,String MM) throws Exception {
        String driverName = "com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql://localhost:3306/ckr";
        String user = "root";
        String password = "1234";
        Statement stmt;
        ResultSet rs;
        Connection coon=JDBC1.getConnection(driverName,url,user,password);
        String sql="select zhanghao,mima from saccount";
        stmt=coon.createStatement();
        rs=stmt.executeQuery(sql);
        while(rs.next()){
            String ZH = rs.getString("zhanghao");
            String MI = rs.getString("mima");
            if(ZH.equals(ZHH)&&MI.equals(MM)){
                return true;
            }
        }
        rs.close();
        stmt.close();
        coon.close();
        return false;
    }
    //确认管理员账号是否正确
    public static boolean find2(String ZHH,String MM) throws Exception {
        String driverName = "com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql://localhost:3306/ckr";
        String user = "root";
        String password = "1234";
        Statement stmt;
        ResultSet rs;
        Connection coon=JDBC1.getConnection(driverName,url,user,password);
        String sql="select zhanghao,mima from maccount";
        stmt=coon.createStatement();
        rs=stmt.executeQuery(sql);
        while(rs.next()){
            String ZH = rs.getString("zhanghao");
            String MI = rs.getString("mima");
            if(ZH.equals(ZHH)&&MI.equals(MM)){
                return true;
            }
        }
        rs.close();
        stmt.close();
        coon.close();
        return false;
    }
    //存储学生账号密码
    public static void save1(String zhanghao,String mima,String Sname) throws Exception {
        String driverName = "com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql://localhost:3306/ckr";
        String user = "root";
        String password = "1234";
        PreparedStatement stmt;
        ResultSet rs;
        Connection coon=JDBC1.getConnection(driverName,url,user,password);
        String sql="INSERT INTO saccount (zhanghao,mima,Sname) VALUES(?,?,?)";
        stmt=coon.prepareStatement(sql);
        stmt.setString(1,zhanghao);
        stmt.setString(2,mima);
        stmt.setString(3,Sname);
        stmt.executeUpdate();
        stmt.close();
        coon.close();
    }
    //存储学生保修信息
    public static void save2(String a,int b,String c,String d)throws Exception{
        String driverName = "com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql://localhost:3306/ckr";
        String user = "root";
        String password = "1234";
        PreparedStatement stmt;
        ResultSet rs;
        Connection coon=JDBC1.getConnection(driverName,url,user,password);
        String sql="INSERT INTO upmix (Sname,BuiltNum1,DormNum1,Reason) VALUES(?,?,?,?)";
        stmt=coon.prepareStatement(sql);
        stmt.setString(1,a);
        stmt.setInt(2,b);
        stmt.setString(3,c);
        stmt.setString(4,d);
        stmt.executeUpdate();
        stmt.close();
        coon.close();
    }

    /**
     *
     * @param zhanghao
     * @return
     * @throws Exception
     */
    public static String getSname(String zhanghao) throws Exception {
        String driverName = "com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql://localhost:3306/ckr";
        String user = "root";
        String password = "1234";
        PreparedStatement stmt;
        ResultSet rs;
        Connection coon=JDBC1.getConnection(driverName,url,user,password);
        String sql="select Sname from saccount where zhanghao=?";
        stmt=coon.prepareStatement(sql);
        stmt.setString(1,zhanghao);
        rs=stmt.executeQuery();
        rs.next();
        return rs.getString("Sname");
    }

    //存储学生选择的宿舍
    public static void save3(String a,String b,String c,String d)throws Exception{
        String driverName = "com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql://localhost:3306/ckr";
        String user = "root";
        String password = "1234";
        PreparedStatement stmt;
        ResultSet rs;
        Connection coon=JDBC1.getConnection(driverName,url,user,password);
        String sql="INSERT INTO sdorm (Sname,BuiltNum,DormNum,BedNum) VALUES(?,?,?,?)";
        stmt=coon.prepareStatement(sql);
        stmt.setString(1,a);
        stmt.setString(2,b);
        stmt.setString(3,c);
        stmt.setString(4,d);
        stmt.executeUpdate();
        stmt.close();
        coon.close();
    }
    //存储管理员的注册信息
    public static void save4(String zhanghao,String mima,String Mname) throws Exception {
        String driverName = "com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql://localhost:3306/ckr";
        String user = "root";
        String password = "1234";
        PreparedStatement stmt;
        ResultSet rs;
        Connection coon=JDBC1.getConnection(driverName,url,user,password);
        String sql="INSERT INTO maccount (zhanghao,mima,MName) VALUES(?,?,?)";
        stmt=coon.prepareStatement(sql);
        stmt.setString(1,zhanghao);
        stmt.setString(2,mima);
        stmt.setString(3,Mname);
        stmt.executeUpdate();
        stmt.close();
        coon.close();
    }
    //删除sdorm表中数据
    public static void delete1(String Sname)throws Exception{
        String driverName = "com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql://localhost:3306/ckr";
        String user = "root";
        String password = "1234";
        PreparedStatement stmt;
        ResultSet rs;
        Connection coon=JDBC1.getConnection(driverName,url,user,password);
        String sql="DELETE FROM sdorm WHERE Sname=?";
        stmt=coon.prepareStatement(sql);
        stmt.setString(1,Sname);
        stmt.executeUpdate();
        stmt.close();
        coon.close();
    }
    //修改sdorm表中数据
    public static void Change1(String Sname,String BuiltNum,String DormNum,String BedNum)throws Exception{
        String driverName = "com.mysql.cj.jdbc.Driver";
        String url="jdbc:mysql://localhost:3306/ckr";
        String user = "root";
        String password = "1234";
        PreparedStatement stmt;
        ResultSet rs;
        Connection coon=JDBC1.getConnection(driverName,url,user,password);
        String sql="UPDATE sdorm SET BuiltNum=?,DormNum=?,BedNum=? WHERE Sname=?";
        stmt=coon.prepareStatement(sql);
        stmt.setString(1,BuiltNum);
        stmt.setString(2,DormNum);
        stmt.setString(3,BedNum);
        stmt.setString(4,Sname);
        stmt.executeUpdate();
        stmt.close();
        coon.close();
    }
}
